import uuid
from datetime import datetime
from typing import List, Optional

from sqlalchemy import Integer, String, ForeignKey, DateTime, Text, create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user"

    id: Mapped[str] = mapped_column(
        String(36), primary_key=True, default=lambda: str(uuid.uuid4())
    )
    name: Mapped[str] = mapped_column(String(32))
    password: Mapped[str] = mapped_column(String(32))
    api_key: Mapped[str] = mapped_column(String(64))

    chats: Mapped[List["Chat"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )


class Chat(Base):
    __tablename__ = "chat"

    id: Mapped[str] = mapped_column(
        String(36), primary_key=True, default=lambda: str(uuid.uuid4())
    )
    mode: Mapped[int] = mapped_column(Integer)
    system_prompt: Mapped[str | None] = mapped_column(Text)
    user_id: Mapped[str] = mapped_column(
        ForeignKey(column="user.id", ondelete="CASCADE")
    )
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.now)
    source: Mapped[str] = mapped_column(String(16), default="web")

    user: Mapped["User"] = relationship(back_populates="chats")
    analysis: Mapped["ChatAnalysis"] = relationship(
        back_populates="chat", uselist=False, cascade="all, delete-orphan"
    )
    messages: Mapped[List["Message"]] = relationship(
        back_populates="chat", cascade="all, delete-orphan"
    )


class Message(Base):
    __tablename__ = "message"

    id: Mapped[str] = mapped_column(
        String(36), primary_key=True, default=lambda: str(uuid.uuid4())
    )
    chat_id: Mapped[str] = mapped_column(
        ForeignKey(column="chat.id", ondelete="CASCADE")
    )
    index: Mapped[int] = mapped_column(Integer)
    role: Mapped[str] = mapped_column(String(16))
    content: Mapped[str] = mapped_column(Text)

    chat: Mapped["Chat"] = relationship(back_populates="messages")
    analysis: Mapped["MessageAnalysis"] = relationship(
        back_populates="message", uselist=False, cascade="all, delete-orphan"
    )


class MessageAnalysis(Base):
    __tablename__ = "message_analysis"

    id: Mapped[str] = mapped_column(
        String(36), primary_key=True, default=lambda: str(uuid.uuid4())
    )
    message_id: Mapped[str] = mapped_column(
        ForeignKey(column="message.id", ondelete="CASCADE")
    )
    language_use_analysis: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    pronunciation_analysis: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    pronunciation_score: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    message: Mapped["Message"] = relationship(back_populates="analysis")


class ChatAnalysis(Base):
    __tablename__ = "chat_analysis"

    id: Mapped[str] = mapped_column(
        String(36), primary_key=True, default=lambda: str(uuid.uuid4())
    )
    chat_id: Mapped[str] = mapped_column(
        ForeignKey(column="chat.id", ondelete="CASCADE")
    )
    language_use_analysis: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    pronunciation_analysis: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
    communication_skill_analysis: Mapped[Optional[str]] = mapped_column(
        Text, nullable=True
    )

    chat: Mapped["Chat"] = relationship(back_populates="analysis")


engine = create_engine("sqlite:///E:/Python/OralPractice/database.db")

if __name__ == "__main__":
    Base.metadata.create_all(engine)
    print("数据库表创建成功。")
